import basedata 
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import fills,colors,NamedStyle,Font,Side,Border,PatternFill,Alignment,Protection
import numpy as np 
import pathlib
import os
import sqlite3
root=pathlib.Path(__file__).parent
db=sqlite3.connect(str(root/"cj.db"))
cur = db.cursor()

class summary():
    def __init__(self):
        self.grade=None
        self.subject=None
        self.classGroup=None
        self.wb=Workbook()
        self.ws=self.wb.active
        self.row=1
        self.col=1
        self.ws.cell(self.row,self.col,basedata.titleH+"平均分汇总").font=basedata.fontTitle
        self.saveWb(str(root/"output/汇总.xlsx"))
    def mainLoop(self):
        self.row=2
        self.col=1
        for i in basedata.km:
            self.ws.cell(self.row,1,i).font=basedata.fontTitle
            self.ws.cell(self.row,1).alignment=basedata.alignment
            n=0
            self.row=self.row +1
            self.col=3
            self.ws.cell(self.row,2,"班级").alignment=basedata.alignment
            self.ws.cell(self.row,1).border=basedata.bd
            self.ws.cell(self.row,2).border=basedata.bd
            for x in basedata.group[i]:
                for x1 in x:
                    if(basedata.alias.__contains__(str(x1))):
                        self.ws.cell(self.row,self.col,basedata.alias[str(x1)]+"班").alignment=basedata.alignment
                    else:
                        self.ws.cell(self.row,self.col,str(x1)+"班").alignment=basedata.alignment
                    self.ws.cell(self.row,self.col).border=basedata.bd
                    self.col=self.col+1
                n=n+len(x)
            self.ws.merge_cells(start_row=self.row-1, end_row=self.row-1, start_column=1, end_column=n+2)#合并
            self.row=self.row+1
            listt=basedata.km[i]
            listt.append("总分")
            for n in listt:
                self.grade=i
                self.subject=n
               
                self.ws.cell(self.row,1,self.subject).border=basedata.bd
                self.ws.cell(self.row,1).alignment=basedata.alignment
                if(basedata.cppjttj):
                    self.ws.merge_cells(start_row=self.row, end_row=self.row+5, start_column=1, end_column=1)#合并
                else:
                    self.ws.merge_cells(start_row=self.row, end_row=self.row+2, start_column=1, end_column=1)#合并
                self.ws.cell(self.row,2,"班级平均分").border=basedata.bd
                self.ws.cell(self.row+1,2,"年级平均分").border=basedata.bd
                self.ws.cell(self.row+2,2,"差值").border=basedata.bd
                if(basedata.cppjttj):
                    self.ws.cell(self.row+3,2,"评比班级平均分").border=basedata.bd
                    self.ws.cell(self.row+4,2,"评比年级平均分").border=basedata.bd
                    self.ws.cell(self.row+5,2,"差值").border=basedata.bd
                    self.gradeLoop()
                    self.row=self.row+6
                else:
                    self.gradeLoop()
                    self.row=self.row+3
                
                self.saveWb(str(root/"output/汇总.xlsx"))

    def gradeLoop(self):
        self.col=3
        for g in basedata.group[self.grade]:
            
            self.classGroup=g
            datas=self.getData()
            da=[]
            groupSize=len(datas)
            n=0
            cprsn={}
            cppjf={} #参评平均分
            xjpjf={}  #实际平均分
            for i in datas:
                cprsn[str(i)]=basedata.getTotal(i,datas[str(i)])
                cppjf[str(i)]=np.nansum(datas[str(i)])/cprsn[str(i)]
                xjpjf[str(i)]=np.nanmean(datas[str(i)])
                da=da+datas[str(i)] #全部分数

            njpjf=np.nanmean(list(cppjf.values()))  #年级参评平均分
            njsjpjf=np.nanmean(list(xjpjf.values())) #年级实际平均分
            #njpjf=np.sum(datas[i])/cprsn[i]



            for i in datas:
                i=str(i)
                self.ws.cell(self.row,self.col+n,float(f"{xjpjf[i]:.{basedata.DecimalPoint}f}")) #实际平均分
                self.ws.cell(self.row+2,self.col+n,float(f"{(xjpjf[i]-njsjpjf):+.{basedata.DecimalPoint}f}"))
                self.ws.cell(self.row,self.col+n).border=basedata.bd
                self.ws.cell(self.row+2,self.col+n).border=basedata.bd
                if(basedata.cppjttj):
                    self.ws.cell(self.row+4,self.col+n,float(f"{cppjf[i]:.{basedata.DecimalPoint}f}"))  #参评平均分
                    self.ws.cell(self.row+6,self.col+n,float(f"{(cppjf[i]-njpjf):+.{basedata.DecimalPoint}f}"))
                    self.ws.cell(self.row+3,self.col+n).border=basedata.bd
                    self.ws.cell(self.row+5,self.col+n).border=basedata.bd
                n=n+1
                print(self.subject,i,np.nanmean(datas[i]))
            self.ws.cell(self.row+1,self.col,float(f"{njsjpjf:.{basedata.DecimalPoint}f}"))
            self.ws.cell(self.row+1,self.col).border=basedata.bd
            self.ws.cell(self.row+1,self.col).border=basedata.bd
            self.ws.cell(self.row+1,self.col).alignment=basedata.alignment
            if(basedata.cppjttj):
                self.ws.cell(self.row+5,self.col,float(f"{njpjf:.{basedata.DecimalPoint}f}"))
                self.ws.cell(self.row+4,self.col).border=basedata.bd
                self.ws.cell(self.row+4,self.col).alignment=basedata.alignment
            if(groupSize>1):
                self.ws.merge_cells(start_row=self.row+1, end_row=self.row+1, start_column=self.col, end_column =self.col + groupSize-1) #合并年级平均分单元格
                if(basedata.cppjttj):
                    self.ws.merge_cells(start_row=self.row+4, end_row=self.row+4, start_column=self.col, end_column =self.col + groupSize-1) #合并年级平均分单元格
            
            
            self.col=self.col+groupSize
        



    def getData(self):
        datas={}
        #datas["all"]=[]
        for i in self.classGroup:
            datas[str(i)]=[]
            sql="select `{}` from main where `班级`= {}".format(self.subject,int(i))
            r=cur.execute(sql)
            for n in r.fetchall():
                if (n[0]=="" or n[0]=="Null" or n[0]==None ):
                    continue
                else:
                    datas[str(i)].append(n[0])
                    # datas[str(i)].append(basedata.zh(self.subject,n[0]))
                    #datas["all"].append(basedata.zh(self.subject,n[0]))
        return datas

    def saveWb(self,path):
        try:
            self.wb.save(path)
        except PermissionError:
            input("文件{path}无法保存，请关闭文件！".format(path=path))
            self.saveWb(path) 
        except Exception as e:
            print(str(e))
sum=summary()
sum.mainLoop()
sum.saveWb(str(root/"output/平均分汇总.xlsx"))
input("已完成汇总，请按Enter退出")